﻿CREATE TRIGGER [DropFunction]
ON DATABASE 
FOR DROP_FUNCTION 
AS 

--SELECT EVENTDATA()
DECLARE @DON sysname -- deleted object name
DECLARE @DOS sysname -- deleted object schema
SET @DON = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)')
SET @DOS = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(max)')
IF @DOS <> 'dbo' SET @DON = @DOS+'.'+@DON

PRINT 'Deleting:'+@DON
DECLARE @RON sysname,@Is sysname -- reffering object name

--SELECT @RON = ISNULL(@RON+',','')+ object_name FROM admin.Dependencies(@DON)

SELECT @RON = ISNULL(@RON+',','')+ o.name
FROM syscomments c WITH(NOLOCK)
inner join  sys.objects o WITH(NOLOCK) on c.id = o.object_id
inner join sys.schemas s on o.schema_id = s.schema_id
WHERE o.name <> @DON AND c.text LIKE '[ .]'+@DON+'(%'

SET @Is = CASE @@ROWCOUNT WHEN 1 THEN 'is' ELSE 'are' END

IF @RON IS NULL RETURN

RAISERROR('%s %s using %s.',16,1,@RON,@Is,@DON)
ROLLBACK



